package com.travelsupport.controller;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

	private final String TAG = com.travelsupport.common.Common.TAG;
	private static String DB_PATH = com.travelsupport.common.Common.DB_FILEPATH;
	private static String DB_NAME = com.travelsupport.common.Common.DB_NAME;
	private static String ASSET_DB_FODER = com.travelsupport.common.Common.ASSETS_DB_FOLDER;

	public static final String TBL_CATALOG = "tblCatalog";
	public static final String TBL_LOCATION = "tblLocation";
	public static final String TBL_PRICEID = "tblPrice";
	public static final String TBL_QUANLITY = "tblQuanlity";
	public static final String TBL_CURRENCY = "tblCurrency";
	public static final String TBL_ADVERTISE ="tblAdvertise";

	public static final String CATALOG_ID = "_id";
	public static final String CATALOG_NAME = "name";
	public static final String CATALOG_ICON = "icon";
	public static final String CATALOG_SUBID = "catalogID";
	public static final String[] ALL_CATALOG = { CATALOG_ID, CATALOG_NAME,
			CATALOG_ICON, CATALOG_SUBID };

	// Currency
	public static final String CURRENCY_ID = "_id";
	public static final String CURRENCY_DATE = "DATE";
	public static final String CURRENCY_USD = "USD";
	public static final String CURRENCY_EUR = "EUR";
	public static final String CURRENCY_YEN = "YEN";
	public static final String CURRENCY_RMB = "RMB";
	public static final String CURRENCY_ALL = "*";
	
	//advertise
	public static final String ADVERTISE_ID = "_id";
	public static final String ADVERTISE_NAME = "adName";
	public static final String ADVERTISE_URL = "adURL";

	// Location
	public static final String LOCATION_ID = "_id";
	public static final String LOCATION_NAME = "name";
	public static final String LOCATION_ADDRESS = "address";
	public static final String LOCATION_PHONE = "phone";
	public static final String LOCATION_WEBSITE = "website";
	public static final String LOCATION_IMAGE = "image";
	public static final String LOCATION_DESCRIPTION = "description";
	public static final String LOCATION_PRICEID = "priceID";
	public static final String LOCATION_SUBCATALOGID = "subCatalogID";
	public static final String LOCATION_LATITUDE = "latitude";
	public static final String LOCATION_LONGITUDE = "longitude";
	public static final String LOCATION_FAVORITE = "favorite";
	public static final String[] ALL_LOCATION = { LOCATION_ID, LOCATION_NAME,
			LOCATION_ADDRESS, LOCATION_PHONE, LOCATION_PHONE, LOCATION_WEBSITE,
			LOCATION_IMAGE, LOCATION_DESCRIPTION, LOCATION_PRICEID,
			LOCATION_SUBCATALOGID, LOCATION_LATITUDE, LOCATION_LONGITUDE,
			LOCATION_FAVORITE };

	private final Context context;
	private SQLiteDatabase myDatabase;

	public MySQLiteHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.context = context;
	}

	public void createDatabase() throws IOException {
		boolean dbExist = checkDatabase();
		Log.d(TAG, "db exist:"+ dbExist);
		if (!dbExist) {
			super.getReadableDatabase();
			try {
				copyDatabase();
			} catch (IOException ioe) {
				throw new Error("Error copying database");
			}
		}
	}

	private boolean checkDatabase() {
		SQLiteDatabase checkDb = null;

		try {
			String myPath = DB_PATH + DB_NAME;
			checkDb = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY
							| SQLiteDatabase.NO_LOCALIZED_COLLATORS);
			Log.i(TAG, "checkDb done");
		} catch (SQLiteException e) {
			// TODO: handle exception

		}
		if (checkDb != null) {
			Log.i(TAG, "checkDB != null");
			checkDb.close();
		}
		return checkDb != null ? true : false;
	}

	private void copyDatabase() throws IOException {
		OutputStream myOutput;
		String[] dbFiles = context.getAssets().list(ASSET_DB_FODER);
		Log.i(TAG, "count dbFile: " + dbFiles.length);
		String outFileName = DB_PATH + DB_NAME;

		try {
			myOutput = new FileOutputStream(outFileName);
			Log.i(TAG, "create outputfile");
		} catch (FileNotFoundException fe) {
			Log.i(TAG, "File not found");
			return;
		}

		for (int i = 0; i < dbFiles.length; i++) {
			InputStream myInput = null;
			try {
				myInput = context.getAssets().open(
						ASSET_DB_FODER + "/" + dbFiles[i]);
				Log.i(TAG, "open inputfile");
			} catch (IOException ioe) {
				Log.i(TAG, "Can not open inputfile");
				return;
			}

			byte[] buffer = new byte[1024];
			int length;

			while ((length = myInput.read(buffer)) > 0) {
				try {
					myOutput.write(buffer, 0, length);
					Log.i(TAG, "Read data: " + length);
				} catch (IOException ioe) {
					Log.i(TAG, "Can not write to outputfile");
					return;
				}
			}
			myInput.close();
		}
		myOutput.flush();
		myOutput.close();
		Log.i(TAG, "done");
	}

	public void openDatabase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		myDatabase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY
						| SQLiteDatabase.NO_LOCALIZED_COLLATORS);

	}

	@Override
	public synchronized void close() {
		// TODO Auto-generated method stub
		if (myDatabase != null)
			myDatabase.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	@Override
	public synchronized SQLiteDatabase getReadableDatabase() {
		try {
			createDatabase();
			openDatabase();
		} catch (SQLException e) {
			myDatabase = null;
			e.printStackTrace();
		} catch (IOException e) {
			myDatabase = null;
			e.printStackTrace();
		}
		return myDatabase;
	}
}
